package me.baicai.weixin.identity.repository;

import com.alibaba.fastjson.JSONObject;
import me.baicai.weixin.entity.identity.Shop;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;


@Repository
public interface ShopRepository extends JpaRepository<Shop, Long> {

    @Query(value = "select s.*,ifnull(SUM(o.amount),0) as amount, ROUND(ifnull((count(o.id) / s.count) / (TO_DAYS(NOW()) -TO_DAYS(s.created)), '0'),2) *100 as rate from (select s.shopName,s.telphone,s.created, s.id,s.openid,s.commission,s.address, s.refund,count(d.id)  as count from shop s LEFT JOIN device d on s.id = d.shopId where s.agentId = ?1  group by s.shopName,s.id) as s left JOIN `order`o on o.shopId = s.id and o.orderStatus = 2 where s.id like ?2 or s.shopName like ?2 or s.address like ?2 GROUP BY s.id",nativeQuery = true)
    List<JSONObject> findAgentShopByInput(String openid, String Input, Pageable pageable);

    @Query(value = "select s.*,ifnull(SUM(o.amount),0) as amount, ROUND(ifnull((count(o.id) / s.count) / (TO_DAYS(NOW()) -TO_DAYS(s.created)), '0'),2) *100 " +
                   "as rate from (select s.shopName,s.telphone, s.id,s.created,s.openid,s.commission,s.address, s.refund ,count(d.id)  as count from shop s LEFT JOIN device d on s.id = d.shopId where s.agentId = ?1  group by s.shopName,s.id) as s left JOIN `order`o on o.shopId = s.id and o.orderStatus = 2 GROUP BY s.id",nativeQuery = true)
    List<JSONObject> findAgentShop(String openid, Pageable pageable);

    @Query(value = "select s.*,count(o.id) as orderCount,ifnull(SUM(o.amount),0) as amount from (select s.id,s.shopName,s.address,s.telphone,s.commission,s.salesmanCommission,s.engCommission,s.refund ,s.latitude,s.longitude,count(d.id) as count, s.created from shop s LEFT JOIN device d ON s.id = d.shopId\n" +
            " where s.id = ?1 GROUP BY s.id) as s LEFT JOIN `order` o on s.id = o.shopId and o.orderStatus = 2 GROUP BY s.id",nativeQuery = true)
    JSONObject findShopById(Integer id);

    @Transactional
    @Modifying
    @Query("update Shop set shopname = ?1 , address =?2 , telphone =?3, commission =?4, refund = ?6, latitude = ?7, longitude = ?8 where id = ?5")
    int sumbitShop(String shopname, String add, String tel, Integer comm, Integer id, Integer refund, String latitude, String longitude);

    List<Shop> findAllByAgentid(String openid);

    @Transactional
    @Modifying
    @Query("update Shop set engId = ?2 where engId = ?1")
    int updateEngId(String id, String newId);

    @Transactional
    @Modifying
    @Query("update Shop set salesmanId = ?2 where salesmanId = ?1")
    int updateSalesmanId(String id, String newId);

    @Transactional
    @Modifying
    @Query("update Shop set engId = '' , engCommission = '0' where engId = ?1")
    int deleteEngineer(String openid);

    @Transactional
    @Modifying
    @Query("update Shop set salesmanId = '' , salesmanCommission = '0' where salesmanId = ?1")
    int deleteSalesman(String openid);

    @Transactional
    @Modifying
    @Query("update Shop set engId = ?1 , engCommission = ?2 where id = ?3")
    int updateEngineer(String openid, Integer commission, Integer id);
    @Transactional
    @Modifying
    @Query("update Shop set salesmanId = ?1 , salesmanCommission = ?2 where id = ?3")
    int updateSalesman(String openid, Integer commission, Integer id);

    @Query(value = "select count(1) from device where shopId in (select s.id from shop s where s.openid = ?1)", nativeQuery = true)
    Integer getShopDeviceCount(String openid);

    @Query(value = "select count(1) from `order` o where o.orderStatus = 2 and o.shopId in (select s.id from shop s where s.openid = ?1)", nativeQuery = true)
    Integer getShopOrderCount(String openid);

    @Query(value = "select ifnull(SUM(amount),0) from money_log where targetId = ?1 and orderSrc = '分成' and to_days(created) = to_days(now())", nativeQuery = true)
    Float findToDayAmount(String openid);

    @Query(value = "select s.id,s.created,s.shopName,s.address,s.commission,ifnull(s.deviceCount,0) as deviceCount,ifnull(p.orderCount,0) as orderCount, ifnull(p.amount,0) as amount from (select s.id,s.created,s.shopName,s.address,s.commission,count(d.sn) as deviceCount from shop s LEFT JOIN device d on s.id = d.shopId where s.openid = ?1 GROUP BY s.id ) as s LEFT JOIN (select  s.id,count(o.id) as orderCount, sum(m.amount) as amount from `order` o LEFT JOIN shop s on o.shopId = s.id LEFT JOIN money_log m on m.orderId = o.id and m.targetType = 'shop' where s.openid = ?1 GROUP BY s.id) as p on s.id = p.id", nativeQuery = true)
    List<JSONObject> getShopInfo(String openid);

    @Query(value = "select s.id,s.created,s.shopName,s.address,s.commission,ifnull(s.deviceCount,0) as deviceCount,ifnull(p.orderCount,0) as orderCount, ifnull(p.amount,0) as amount from (select s.id,s.created,s.shopName,s.address,s.commission,count(d.sn) as deviceCount from shop s LEFT JOIN device d on s.id = d.shopId where s.openid = ?1 and s.shopName like ?2 GROUP BY s.id ) as s LEFT JOIN (select  s.id,count(o.id) as orderCount, sum(m.amount) as amount from `order` o LEFT JOIN shop s on o.shopId = s.id LEFT JOIN money_log m on m.orderId = o.id and m.targetType = 'shop' where s.openid = ?1 GROUP BY s.id) as p on s.id = p.id" , nativeQuery = true)
    List<JSONObject> getShopInfoByInput(String openid, String input);


    @Query(value = "select s.id as shopId,s.shopName,s.address, s.engCommission as commission ,s.salesmanCommission  as otherCommission,s.commission  as shopCommission,e.`name`,i.id  from shop s LEFT JOIN employee e on e.openid = s.engId LEFT JOIN identity i on e.openid = i.openid where s.agentId = ?1" ,nativeQuery = true)
    List<JSONObject> findAllByAgentidEngineer(String openid);

    @Query(value = "select s.id as shopId,s.shopName,s.address,s.engCommission  as otherCommission ,s.salesmanCommission  as commission,s.commission  as shopCommission,e.`name`,i.id  from shop s LEFT JOIN employee e on e.openid = s.salesmanId LEFT JOIN identity i on e.openid = i.openid where s.agentId = ?1" ,nativeQuery = true)
    List<JSONObject> findAllByAgentidSalesman(String openid);


    @Query(value = "select date(m.created) as created , sum(m.amount) as amount from money_log m where m.orderSrc = '分成' and m.targetType = ?4 and m.targetId = ?1 and  created between ?2 and date_add(?3, interval 1 day) GROUP BY date(m.created) ORDER BY date(m.created) desc", nativeQuery = true)
    List<JSONObject> incomeStatistics(String openid, String beginDate, String endDate, String type);

    @Query(value = "select o.id,o.created,o.amount as orderAmount,m.amount, s.shopName ,s.commission from `order` o LEFT JOIN money_log m on o.id = m.orderId and m.targetType = ?3 LEFT JOIN shop s on s.id = o.shopId where TO_DAYS(m.created) = TO_DAYS(?2) and m.targetId = ?1 ORDER BY date(m.created) desc ", nativeQuery = true)
    List<JSONObject> orderStatistics(String openid, String date, String type);

    @Query(value = "select s.*,count(d.id) as deviceCount from shop s LEFT JOIN device d on d.shopId = s.id where s.engId = ?1  GROUP BY s.id",nativeQuery = true)
    List<JSONObject> ManageShopByEngineer(String openid, Pageable pageable);

    @Query(value = "select s.*,count(d.id) as deviceCount from shop s LEFT JOIN device d on d.shopId = s.id where s.salesmanId = ?1 GROUP BY s.id",nativeQuery = true)
    List<JSONObject> ManageShopBySalesman(String openid, Pageable pageable);

    @Query(value = "select s.*,count(d.id) as deviceCount from shop s LEFT JOIN device d on d.shopId = s.id where s.engId = ?1 and s.shopName like ?2 GROUP BY s.id",nativeQuery = true)
    List<JSONObject> ManageShopByEngineerInput(String openid, String input, Pageable pageable);

    @Query(value = "select s.*,count(d.id) as deviceCount from shop s LEFT JOIN device d on d.shopId = s.id where s.salesmanId = ?1 and s.shopName like ?2 GROUP BY s.id",nativeQuery = true)
    List<JSONObject> ManageShopBySalesmanInput(String openid, String input, Pageable pageable);

    @Query(value = "select s.*,count(d.id) as deviceCount from shop s LEFT JOIN device d on d.shopId = s.id where s.agentId = ?1 GROUP BY s.id",nativeQuery = true)
    List<JSONObject> ManageShopByAgent(String openid, Pageable pageable);

    @Query(value = "select s.*,count(d.id) as deviceCount from shop s LEFT JOIN device d on d.shopId = s.id where s.agentId = ?1 and s.shopName like ?2 GROUP BY s.id",nativeQuery = true)
    List<JSONObject> ManageShopByAgentInput(String openid, String input, Pageable pageable);

    List<Shop> findAllByEngIdOrSalesmanId(String openid, String id);

    Shop getById(Integer id);

    boolean existsByIdAndAgentid(Integer id, String openid);

    @Transactional
    int deleteById(Integer id);


    @Query(value = "select s.telphone,t.`name`,s.commission, t.id, t.avatarUrl from shop s LEFT JOIN identity t on t.openid = s.openid where s.id = ?1", nativeQuery = true)
    JSONObject shopStaff(Integer id);


    @Query(value = "select s.*,ifnull(SUM(o.amount),0) as amount, ROUND(ifnull((count(o.id) / s.count) / (TO_DAYS(NOW()) -TO_DAYS(s.created)), '0'),2) *100 " +
                   "as rate from (select s.shopName,s.telphone, s.id,s.created,s.openid,s.commission,s.address,count(d.id)  as count from shop s LEFT JOIN device d on s.id = d.shopId where s.agentId = ?1  group by s.shopName,s.id) as s left JOIN `order`o on o.shopId = s.id GROUP BY s.id", countQuery = "select count(1) from shop s where s.agentId = ?1", nativeQuery = true)
    Page<JSONObject> findAgentShopA(String openid, Pageable pageable);

    @Query(value = "SELECT i.`name`,i.openid,i.id,i.nickName FROM identity i where i.openid in(select s.openid from shop s where s.agentId = ?1)", nativeQuery = true)
    List<JSONObject> getAllByAgentid(String openid);

    @Query(value = "select s.*,ifnull(SUM(o.amount),0) as amount, count(o.id) as orderCount, ROUND(ifnull((count(o.id) / s.count) / (TO_DAYS(NOW()) -TO_DAYS(s.created)), '0'),2) *100 as rate from (select s.shopName,s.telphone, s.id,s.created,s.openid,s.commission,s.address, s.refund ,count(d.id)  as count from shop s LEFT JOIN device d on s.id = d.shopId LEFT JOIN agent a on a.openid = s.agentId where a.`level` = ?1  group by s.shopName,s.id) as s left JOIN `order`o on o.shopId = s.id GROUP BY s.id", nativeQuery = true)
    List<JSONObject> allShop(int level, Pageable pageable);

    @Query(value = "select s.*,ifnull(SUM(o.amount),0) as amount, count(o.id) as orderCount, ROUND(ifnull((count(o.id) / s.count) / (TO_DAYS(NOW()) -TO_DAYS(s.created)), '0'),2) *100 as rate from (select s.shopName,s.telphone, s.id,s.created,s.openid,s.commission,s.address, s.refund ,count(d.id)  as count from shop s LEFT JOIN device d on s.id = d.shopId LEFT JOIN agent a on a.openid = s.agentId where a.`level` = ?1 and s.shopName like ?2 or a.`level` = ?1 and s.id like ?2 group by s.shopName,s.id) as s left JOIN `order`o on o.shopId = s.id GROUP BY s.id", nativeQuery = true)
    List<JSONObject> allShopByValue(int i, String s, Pageable pageable);

    @Query(value = "select t.*,b.count from (select t.*,count(s.id) as shopCount from (select t.*,a.cumulative as amount from agent t  LEFT JOIN account a on a.openid = t.openid where t.`level` = ?1) as t LEFT JOIN shop s on s.agentId = t.openid  GROUP BY t.id) as t LEFT JOIN (select t.*,count(d.id) as count from agent t LEFT JOIN shop s on s.agentId = t.openid LEFT JOIN device d on d.shopId = s.id where t.`level` = ?1 GROUP BY t.id) as b on b.id = t.id ", nativeQuery = true)
    List<JSONObject> allAgent(int i, Pageable pageable);

    @Query(value = "select t.*,b.count from (select t.*,count(s.id) as shopCount from (select t.*,a.cumulative as amount from agent t  LEFT JOIN account a on a.openid = t.openid where t.`name` like ?2 or t.id like ?2 and t.`level` = 2) as t LEFT JOIN shop s on s.agentId = t.openid  GROUP BY t.id) as t LEFT JOIN (select t.*,count(d.id) as count from agent t LEFT JOIN shop s on s.agentId = t.openid LEFT JOIN device d on d.shopId = s.id where t.`name` like ?2 or t.id like ?2 and t.`level` = 2 GROUP BY t.id) as b on b.id = t.id ", nativeQuery = true)
    List<JSONObject> allAgentByValue(int i, String s, Pageable pageable);

    @Query(value = "select s.*,ifnull(SUM(o.amount),0) as amount, count(o.id) as orderCount, ROUND(ifnull((count(o.id) / s.count) / (TO_DAYS(NOW()) -TO_DAYS(s.created)), '0'),2) *100 as rate, t.`name`, t.id as agentId from (select s.engCommission, s.engId,s.salesmanId,  s.salesmanCommission, s.shopName,s.telphone, s.id,s.created,s.openid,s.commission,s.address, s.agentId as aid, count(d.id)  as count from shop s LEFT JOIN device d on s.id = d.shopId  group by s.shopName,s.id) as s left JOIN `order`o on o.shopId = s.id LEFT JOIN agent t on t.openid = s.aid where o.orderStatus = '2' GROUP BY s.id, t.id", nativeQuery = true)
    List<JSONObject> findAllByPage(Pageable pageable);

    @Query(value = "select s.*,ifnull(SUM(o.amount),0) as amount, count(o.id) as orderCount, ROUND(ifnull((count(o.id) / s.count) / (TO_DAYS(NOW()) -TO_DAYS(s.created)), '0'),2) *100 as rate, t.`name`, t.id as agentId from (select s.engCommission, s.engId,s.salesmanId,  s.salesmanCommission, s.shopName,s.telphone, s.id,s.created,s.openid,s.commission,s.address, s.agentId as aid, count(d.id)  as count from shop s LEFT JOIN device d on s.id = d.shopId  group by s.shopName,s.id) as s left JOIN `order`o on o.shopId = s.id LEFT JOIN agent t on t.openid = s.aid where s.id like ?1 and o.orderStatus = '2' GROUP BY s.id, t.id", nativeQuery = true)
    List<JSONObject> findAllById(String id, Pageable pageable);

    @Query(value = "select count(1) from shop where id like ?1", nativeQuery = true)
    Integer findAllByIdCount(String s);

    @Query(value = "select s.*,ifnull(SUM(o.amount),0) as amount, count(o.id) as orderCount, ROUND(ifnull((count(o.id) / s.count) / (TO_DAYS(NOW()) -TO_DAYS(s.created)), '0'),2) *100 as rate, t.`name`, t.id as agentId from (select s.engCommission, s.engId,s.salesmanId,  s.salesmanCommission, s.shopName,s.telphone, s.id,s.created,s.openid,s.commission,s.address, s.agentId as aid, count(d.id)  as count from shop s LEFT JOIN device d on s.id = d.shopId  group by s.shopName,s.id) as s left JOIN `order`o on o.shopId = s.id LEFT JOIN agent t on t.openid = s.aid where s.shopName like ?1 and o.orderStatus = '2'  GROUP BY  s.id, t.id ", nativeQuery = true)
    List<JSONObject> findAllByShopnameLike(String id, Pageable pageable);

    @Query(value = "select count(1) from shop where shopName like ?1", nativeQuery = true)
    Integer findAllByShopnameLikeCount(String s);

    @Query(value = "select s.*,ifnull(SUM(o.amount),0) as amount, count(o.id) as orderCount, ROUND(ifnull((count(o.id) / s.count) / (TO_DAYS(NOW()) -TO_DAYS(s.created)), '0'),2) *100 as rate, t.`name`, t.id as agentId from (select s.engCommission, s.engId,s.salesmanId,  s.salesmanCommission, s.shopName,s.telphone, s.id,s.created,s.openid,s.commission,s.address, s.agentId as aid, count(d.id)  as count from shop s LEFT JOIN device d on s.id = d.shopId  group by s.shopName,s.id) as s left JOIN `order`o on o.shopId = s.id LEFT JOIN agent t on t.openid = s.aid where t.id like  ?1 and o.orderStatus = '2'  GROUP BY  s.id, t.id ", nativeQuery = true)
    List<JSONObject> findAllByAgentidLike(String id, Pageable pageable);

    @Query(value = "select count(1) from shop s LEFT JOIN agent a on a.openid = s.agentId where a.id like ?1 ", nativeQuery = true)
    Integer findAllByAgentidLikeCount(String s);


    @Query(value = "select s.*,ifnull(SUM(o.amount),0) as amount, count(o.id) as orderCount, ROUND(ifnull((count(o.id) / s.count) / (TO_DAYS(NOW()) -TO_DAYS(s.created)), '0'),2) *100 as rate, t.`name`, t.id as agentId from (select s.engCommission, s.engId,s.salesmanId,  s.salesmanCommission, s.shopName,s.telphone, s.id,s.created,s.openid,s.commission,s.address, s.agentId as aid, count(d.id)  as count from shop s LEFT JOIN device d on s.id = d.shopId  group by s.shopName,s.id) as s left JOIN `order`o on o.shopId = s.id LEFT JOIN agent t on t.openid = s.aid where t.`name` like  ?1 GROUP BY  s.id, t.id ", nativeQuery = true)
    List<JSONObject> findAllByAgentNameLike(String id, Pageable pageable);

    @Query(value = "select count(1) from shop s LEFT JOIN agent a on a.openid = s.agentId where a.`name` like ?1 ", nativeQuery = true)
    Integer findAllByAgentNameLikeCount(String s);


}
